package com.paad.todolist;

import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class ShoppinglistsDBAdapter {
  private static final String DATABASE_NAME = "shoppingList.db";
  private static final String SHOPPINGLIST_TABLE = "shoppinglists";
  private static final String ITEM_TABLE = "items";
  private static final String LISTING_TABLE = "listings";
  private static final int DATABASE_VERSION = 3;
 
  public static final String KEY_ID = "_id";
  public static final String KEY_SHOPPINGLIST_NAME = "name";
  public static final String KEY_SHOPPINGLIST_DATE = "date";
  public static final String KEY_ITEM_DATE = "lastordered";
  public static final String KEY_ITEM_NAME = "name";
  //public static final String KEY_ITEM_DONE = "done";
  public static final String KEY_LISTING_ITEM_ID = "listing_item_id";
  public static final String KEY_LISTING_LIST_ID = "listing_list_id";
  
  
  private SQLiteDatabase db;
  private final Context context;
  private ShoppinglistDBOpenHelper dbHelper;

  public ShoppinglistsDBAdapter(Context _context) {
    this.context = _context;
    dbHelper = new ShoppinglistDBOpenHelper(context, DATABASE_NAME, 
                                    null, DATABASE_VERSION);
  }
  
  public void close() {
    db.close();
  }
  
  public void open() throws SQLiteException {  
    try {
      db = dbHelper.getWritableDatabase();
    } catch (SQLiteException ex) {
      db = dbHelper.getReadableDatabase();
    }
  }  
  
  //Insert a new task
  public long insertShoppinglist(String shoppinglistName) {
	    // Create a new row of values to insert.
	    ContentValues newTaskValues = new ContentValues();
	    // Assign values for each row.
	    newTaskValues.put(KEY_SHOPPINGLIST_NAME, shoppinglistName);
	    Date now = new Date(java.lang.System.currentTimeMillis());
	    newTaskValues.put(KEY_SHOPPINGLIST_DATE, now.getTime());
	    // Insert the row.
	    return db.insert(SHOPPINGLIST_TABLE, null, newTaskValues);
	  }
  
  // Insert a new shoppinglist item
  public long insertShoppinglistItem(String item_name, Date lastOrdered) {
	    // Create a new row of values to insert.
	    ContentValues newTaskValues = new ContentValues();
	    // Assign values for each row.
	    newTaskValues.put(KEY_ITEM_NAME, item_name);
	    //newTaskValues.put(KEY_ITEM_DONE, false);
	    newTaskValues.put(KEY_ITEM_DATE, lastOrdered.getDate());
	    // Insert the row.
	    return db.insert(ITEM_TABLE, null, newTaskValues);	  
  }
  
  public long insertListing(int _item_id, int _list_id)
  {
	  ContentValues newTaskValues = new ContentValues();
	  newTaskValues.put(KEY_LISTING_ITEM_ID, _item_id);
	  newTaskValues.put(KEY_LISTING_LIST_ID, _list_id);
	  return db.insert(LISTING_TABLE, null, newTaskValues);
  }
  
  // Sets the done state of a shoppinglist item
  /*public void setDone(Integer item_id, Boolean is_done) {
	  ContentValues values = new ContentValues();
	  //values.put(KEY_ITEM_DONE, is_done);
	  db.update(ITEMS_TABLE, values, KEY_ID + "= ?", new String[] {Integer.toString(item_id)});
  }*/
  
  // Remove a task based on its index
  public boolean removeShoppinglist(long _rowIndex) {
    return db.delete(SHOPPINGLIST_TABLE, KEY_ID + "= ?", new String[] {Long.toString(_rowIndex)}) > 0;
  }

  // Update a task
  public boolean updateShoppinglist(long _rowIndex, String _task) {
    ContentValues newValue = new ContentValues();
    newValue.put(KEY_SHOPPINGLIST_NAME, _task);
    return db.update(SHOPPINGLIST_TABLE, newValue, KEY_ID + "=" + _rowIndex, null) > 0;
  }
  
  public Cursor getAllShoppinglistsCursor() {
    return db.query(SHOPPINGLIST_TABLE, 
                    new String[] { KEY_ID, KEY_SHOPPINGLIST_NAME, KEY_SHOPPINGLIST_DATE}, 
                    null, null, null, null, null);
  }

 public Cursor getShoppinglistItemsCursor(Integer shoppinglist_id) {
	return db.query(LISTING_TABLE, new String[] {KEY_ID, KEY_LISTING_ITEM_ID, KEY_LISTING_LIST_ID}, 
			KEY_LISTING_LIST_ID + "= ?", new String[] {Integer.toString(shoppinglist_id)}, 
			null, null, null);
  }
 
 // returns an item given its foreign key
 public Cursor getItemCursor(Integer item_id) {
		return db.query(ITEM_TABLE, new String[] {KEY_ID, KEY_ITEM_NAME, KEY_ITEM_DATE}, 
				KEY_ID + "= ?", new String[] {Integer.toString(item_id)}, 
				null, null, null);
	  }
 
 
  
  public Cursor setCursorToShoppinglist(long _rowIndex) throws SQLException {
    Cursor result = db.query(true, SHOPPINGLIST_TABLE, 
	                           new String[] {KEY_ID, KEY_SHOPPINGLIST_NAME},
                             KEY_ID + "=" + _rowIndex, null, null, null, 
                             null, null);
    if ((result.getCount() == 0) || !result.moveToFirst()) {
      throw new SQLException("No shoppinglist found for row: " + _rowIndex);
    }
    return result;
  }

  public ShoppinglistEntity getShoppinglist(long _rowIndex) throws SQLException {
    Cursor cursor = db.query(true, SHOPPINGLIST_TABLE, 
                             new String[] {KEY_ID, KEY_SHOPPINGLIST_NAME},
                             KEY_ID + "=" + _rowIndex, null, null, null, 
                             null, null);
    if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
      throw new SQLException("No to do item found for row: " + _rowIndex);
    }

    String name = cursor.getString(cursor.getColumnIndex(KEY_SHOPPINGLIST_NAME));
    long created = cursor.getLong(cursor.getColumnIndex(KEY_SHOPPINGLIST_DATE));
		  
    ShoppinglistEntity result = new ShoppinglistEntity((int) _rowIndex, name, new Date(created));
    return result;
  }
  
  
  /*private static final String LIST_DATABASE_CREATE = "create table " + 
		    SHOPPINGLIST_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " +
		    KEY_SHOPPINGLIST_NAME + " text not null, " + KEY_SHOPPINGLIST_DATE + " long);";
  private static final String ITEM_DATABASE_CREATE = "create table " + 
		    ITEM_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " +
		    KEY_ITEM_NAME + " text not null, " + KEY_ITEM_DATE + " long);";
  
  public void updateTables(){
	  db.execSQL("DROP TABLE IF EXISTS " + SHOPPINGLIST_TABLE);
	  db.execSQL("DROP TABLE IF EXISTS " + ITEM_TABLE);
	  bd.execSQL("DROP TABLE IF EXISTS " + LISTING_TABLE);
	  db.execSQL(LIST_DATABASE_CREATE);
	  db.execSQL(ITEM_DATABASE_CREATE);
  }*/
  
  
  private static class ShoppinglistDBOpenHelper extends SQLiteOpenHelper {

	  public ShoppinglistDBOpenHelper(Context context, String name,
	                          CursorFactory factory, int version) {
	    super(context, name, factory, version);
	  }

	  // SQL Statement to create a new database.
	  private static final String DATABASE_CREATE_SHOPPINGLIST_TABLE = "create table " + 
	    SHOPPINGLIST_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " +
	    KEY_SHOPPINGLIST_NAME + " text not null, " + KEY_SHOPPINGLIST_DATE + " long);";

	  private static final String DATABASE_CREATE_ITEM_TABLE = "create table " + 
			    ITEM_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " +
			    KEY_ITEM_NAME + " text not null, " + KEY_ITEM_DATE + " long);";
	  
	  private static final String DATABASE_CREATE_LISTING_TABLE = "create table " + 
	  LISTING_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " + 
			  KEY_LISTING_ITEM_ID + " int references \"" + ITEM_TABLE +
				"\"(\"" + KEY_ID + "\"), " +
			  KEY_LISTING_LIST_ID + " int references \"" + SHOPPINGLIST_TABLE +
				"\"(\"" + KEY_ID + "\") );";
	  /*private static final String DATABASE_CREATE_ITEMS_TABLE = "create table " + 
	    ITEM_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " +
		KEY_ITEM_SHOPPINGLIST_ID + " int references \"" + SHOPPINGLIST_TABLE +
		"\"(\"" + KEY_ID + "\"), " +
	    KEY_ITEM_NAME + " text not null);";*/
	  
	  @Override
	  public void onCreate(SQLiteDatabase _db) {
	    _db.execSQL(DATABASE_CREATE_SHOPPINGLIST_TABLE);
	    _db.execSQL(DATABASE_CREATE_ITEM_TABLE);
	    _db.execSQL(DATABASE_CREATE_LISTING_TABLE);
	  }

	  @Override
	  public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
	    Log.w("TaskDBAdapter", "Upgrading from version " + 
	                           _oldVersion + " to " +
	                           _newVersion + ", which will destroy all old data");

	    // Drop the old table.
	    _db.execSQL("DROP TABLE IF EXISTS " + SHOPPINGLIST_TABLE);
	    _db.execSQL("DROP TABLE IF EXISTS " + ITEM_TABLE);
	    _db.execSQL("DROP TABLE IF EXISTS " + LISTING_TABLE);
	    // Create a new one.
	    onCreate(_db);
	  }
	}
}